package com.qf.mapper;

import com.qf.vo.CategorySellAmts;
import com.qf.vo.DayStatis;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface StatMapper {

    @Select("SELECT c.`name`, SUM(og.`price`) value " +
            "FROM dts_category c, dts_goods g, dts_order o, dts_order_goods og " +
            "WHERE c.`id` = g.`category_id` AND g.id = og.`goods_id` AND o.`id` = og.`order_id` " +
            "GROUP BY c.`name`")
    List<CategorySellAmts> findCategorySellData();

    @Select("SELECT DATE_FORMAT(add_time, '%Y-%m-%d') dayStr, COUNT(1) cnts " +
            "FROM dts_user " +
            "WHERE DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'), INTERVAL #{rang} DAY) " +
            "GROUP BY DATE_FORMAT(add_time, '%Y-%m-%d')")
    List<DayStatis> queryDayStatis(int rang);

    @Select("SELECT DATE_FORMAT(add_time, '%Y-%m-%d') dayStr, COUNT(1) cnts, SUM(order_price) amts " +
            "FROM dts_order " +
            "WHERE add_time > DATE_SUB(DATE_FORMAT(NOW(), '%Y-%m-%d 00:00:00'), INTERVAL 30 DAY) AND order_status NOT IN(101,102,103) AND deleted = 0 " +
            "GROUP BY DATE_FORMAT(add_time, '%Y-%m-%d')")
    List<DayStatis> queryOrderStatis(int rang);


}
